package com.suray.wcs.service.core.util;

import com.suray.basic.wcs.path.core.Node;
import com.suray.basic.wcs.path.enums.Direction;
import com.suray.basic.wcs.path.enums.DirectionStatus;
import com.suray.basic.wcs.path.enums.NodeStatus;
import com.suray.basic.wcs.path.enums.NodeType;
import com.suray.basic.wcs.path.properties.PathProperties;
import com.suray.basic.wcs.utils.Coord;
import com.suray.commin.core.utils.UUID;
import com.suray.wcs.service.execption.IllegalCellException;
import com.suray.wcs.service.system.entity.NodeDB;
import com.suray.wcs.service.system.service.impl.NodeDBServiceImpl;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import java.util.Optional;
import java.util.stream.Collectors;

/**
 * 读取Excel工具类
 */
@Component
public class ReadExcel {
    private static final Logger looger = LoggerFactory.getLogger(LockNodeManager.class);
    @Autowired
    private NodeDBServiceImpl nodeDBService;

    /**
     * 从输入流中读取EXCEL信息
     * 生成node表
     *
     * @param inputStream
     * @return void
     */
    public String readData(InputStream inputStream, boolean updateSupport) throws IOException {
        //导入文件过大
        ZipSecureFile.setMinInflateRatio(-1.0d);
        Workbook workbook = WorkbookFactory.create(inputStream);
        List<NodeDB> nodeDBS = new ArrayList<>();
        if (updateSupport) {
            for (int sheetNo = 0; sheetNo < workbook.getNumberOfSheets(); sheetNo++) {
                Sheet sheet = workbook.getSheetAt(sheetNo);
                // getLastRowNum，获取最后一行的行标
                for (int rowNo = 2; rowNo < sheet.getPhysicalNumberOfRows(); rowNo++) {
                    Row row = sheet.getRow(rowNo);
                    if (row != null) {
                        for (int cellNo = 2; cellNo < row.getPhysicalNumberOfCells(); cellNo++) {
                            RichTextString richStringCellValue = row.getCell(cellNo).getRichStringCellValue();
                            if (richStringCellValue == null || richStringCellValue.toString().equals("")) {
                                return excelExceptionMessage(sheetNo, rowNo, cellNo, "单元格数据不可为空！");
                            }
                            NodeDB nodeDB = nodeDBService.getNodeDB(new Coord(rowNo - 1, cellNo - 1, sheetNo + 1));
                            String cellStr = richStringCellValue.toString();
                            try {
                                sheet.getRow(0).getCell(cellNo).setCellType(CellType.STRING);
                                sheet.getRow(rowNo).getCell(0).setCellType(CellType.STRING);
                            } catch (NullPointerException e) {
                                return excelExceptionMessage(sheetNo, rowNo, cellNo, "数据为空，不可为空!！");
                            }
                            String offsetXStr = sheet.getRow(rowNo).getCell(0).getStringCellValue().trim();
                            String offsetYStr = sheet.getRow(0).getCell(cellNo).getStringCellValue().trim();
                            double doublex = Double.valueOf(offsetXStr);
                            double doubley = Double.valueOf(offsetYStr);
                            Integer offsetX = (int) doublex;
                            Integer offsetY = (int) doubley;
                            if (offsetX < 0 || offsetY < 0 || offsetY == 0 && cellNo != 2 || offsetX == 0 && rowNo != 2) {
                                return excelExceptionMessage(sheetNo, rowNo, cellNo, "位移量不可以小于0！除首行首列外，不可出现0位移量!！");
                            }
                            nodeDB.setOffsetx(offsetX);
                            nodeDB.setOffsety(offsetY);
                            //读取单元格位移量
                            int index1 = cellStr.indexOf("{");
                            int index2 = cellStr.indexOf("}");
                            if (index1 != -1 && index2 != -1) {
                                String offsetXY = cellStr.substring(index1 + 1, index2);
                                String[] offsets = offsetXY.split(",");
                                if (offsets.length == 2) {
                                    if (!offsets[0].trim().equals("")) {
                                        offsetX = Integer.parseInt(offsets[0].trim());
                                        nodeDB.setOffsetx(offsetX);
                                    }
                                    if (!offsets[1].trim().equals("")) {
                                        offsetY = Integer.parseInt(offsets[1].trim());
                                        nodeDB.setOffsety(offsetY);
                                    }
                                } else {
                                    throw new IllegalCellException(new Node(nodeDB.getGridx(), nodeDB.getGridy() - 2, nodeDB.getGridz()), "单元格位移量数据异常！" + cellStr);
                                }
                            }
                            nodeDBS.add(nodeDB);
                        }
                    }
                }
                looger.info("更新sheet表：" + workbook.getSheetName(sheetNo) + " 完成");
            }
            nodeDBService.updateBatchById(nodeDBS);
        } else {
            for (int sheetNo = 0; sheetNo < workbook.getNumberOfSheets(); sheetNo++) {
                Sheet sheet = workbook.getSheetAt(sheetNo);
                // getLastRowNum，获取最后一行的行标
                for (int rowNo = 2; rowNo < sheet.getPhysicalNumberOfRows(); rowNo++) {
                    Row row = sheet.getRow(rowNo);
                    if (row != null) {
                        for (int cellNo = 2; cellNo < row.getPhysicalNumberOfCells(); cellNo++) {
                            RichTextString richStringCellValue = row.getCell(cellNo).getRichStringCellValue();
                            if (richStringCellValue == null || richStringCellValue.toString().equals("")) {
                                return excelExceptionMessage(sheetNo, rowNo, cellNo, "单元格数据不可为空！");
                            }
                            NodeDB nodeDB = new NodeDB();
                            // 系统视图横向为X方向，纵向为Y方向
                            nodeDB.setGridx(rowNo - 1);
                            nodeDB.setGridy(cellNo - 1);
                            nodeDB.setGridz(sheetNo + 1);
                            nodeDB.setGroundCode(createGroundCode(nodeDB));
                            // 货位编码和地码一致
                            nodeDB.setGridId(createGroundCode(nodeDB));
                            // 节点类型
                            String cellStr = richStringCellValue.toString();
                            if (rowNo == 2 && cellStr.contains("上")) {
                                return excelExceptionMessage(sheetNo, rowNo, cellNo, "越界,不可上！");
                            } else if (rowNo == sheet.getPhysicalNumberOfRows() - 1 && cellStr.contains("下")) {
                                return excelExceptionMessage(sheetNo, rowNo, cellNo, "越界,不可下！");
                            } else if (cellNo == 2 && cellStr.contains("左")) {
                                return excelExceptionMessage(sheetNo, rowNo, cellNo, "越界,不可左！");
                            } else if (cellNo == row.getPhysicalNumberOfCells() - 1 && cellStr.contains("右")) {
                                return excelExceptionMessage(sheetNo, rowNo, cellNo, "越界,不可右！");
                            }
                            try {
                                sheet.getRow(0).getCell(cellNo).setCellType(CellType.STRING);
                                sheet.getRow(rowNo).getCell(0).setCellType(CellType.STRING);
                            } catch (NullPointerException e) {
                                return excelExceptionMessage(sheetNo, rowNo, cellNo, "数据为空，不可为空!！");
                            }
                            String offsetXStr = sheet.getRow(rowNo).getCell(0).getStringCellValue().trim();
                            String offsetYStr = sheet.getRow(0).getCell(cellNo).getStringCellValue().trim();
                            double doublex = Double.valueOf(offsetXStr);
                            double doubley = Double.valueOf(offsetYStr);
                            Integer offsetX = (int) doublex;
                            Integer offsetY = (int) doubley;
                            if (offsetX < 0 || offsetY < 0 || offsetY == 0 && cellNo != 2 || offsetX == 0 && rowNo != 2) {
                                return excelExceptionMessage(sheetNo, rowNo, cellNo, "位移量不可以小于0！除首行首列外，不可出现0位移量!！");
                            }
                            nodeDB.setOffsetx(offsetX);
                            nodeDB.setOffsety(offsetY);
                            checkCell(cellNo, rowNo, sheetNo, cellStr, nodeDB);
                            nodeDB.setGridStatus(NodeStatus.N.toString());
                            nodeDBS.add(nodeDB);
                        }
                    }
                }
                looger.info("读取sheet表：" + workbook.getSheetName(sheetNo) + " 完成");
            }

            //给nodeDBS排序,影响效率
            nodeDBS.sort(Comparator.comparingInt(NodeDB::getGridx));
            nodeDBS.sort(Comparator.comparingInt(NodeDB::getGridy));
            nodeDBS.sort(Comparator.comparingInt(NodeDB::getGridz));
            // 巷道划分
            rodeDivide(nodeDBS, null);
            // 清空原来的数据
            nodeDBService.remove(null);
            nodeDBService.saveBatch(nodeDBS);
        }
        return "导入成功,共计" + nodeDBS.size() + "条数据！";
    }

    /**
     * 生成地码
     *
     * @param nodeDB
     * @return
     */
    private String createGroundCode(NodeDB nodeDB) {
        StringBuilder sb = new StringBuilder();
        sb.append(String.format("%0" + 2 + "d", nodeDB.getGridx()));
        sb.append(String.format("%0" + 2 + "d", nodeDB.getGridy()));
        sb.append(String.format("%0" + 2 + "d", nodeDB.getGridz()));
        return sb.toString();
    }

    /**
     * 巷道坡道深位划分
     *
     * @param nodeDBS
     */
    public List<NodeDB> rodeDivide(List<NodeDB> nodeDBS, List<Integer> singleDivide) {
        List<NodeDB> temNodeDBS = new ArrayList<>(nodeDBS);
        List<NodeDB> collect = nodeDBS.stream().filter(nodeDB -> nodeDB.getGridType().equals(NodeType.R)).collect(Collectors.toList());
        collect.forEach(curNodeDB -> {
            mergeByDirection(UUID.randomUUID().toString(), new ArrayList<>(), temNodeDBS, curNodeDB, Direction.UP, singleDivide);
            mergeByDirection(UUID.randomUUID().toString(), new ArrayList<>(), temNodeDBS, curNodeDB, Direction.DOWN, singleDivide);
            mergeByDirection(UUID.randomUUID().toString(), new ArrayList<>(), temNodeDBS, curNodeDB, Direction.LEFT, singleDivide);
            mergeByDirection(UUID.randomUUID().toString(), new ArrayList<>(), temNodeDBS, curNodeDB, Direction.RIGHT, singleDivide);
        });
        return nodeDBS;
    }

    /**
     * 向direction方向查找同巷道的节点并合并
     *
     * @param rodeNo     巷道编号
     * @param nodeDBList 存放当前巷道数据的集合
     * @param nodeDBS    所有的节点
     * @param nodeDB     当前节点
     * @param direction  遍历方向
     */
    private void mergeByDirection(String rodeNo, List<NodeDB> nodeDBList, List<NodeDB> nodeDBS, NodeDB nodeDB, Direction direction, List<Integer> singleDivide) {
        Optional<NodeDB> nextNodeDB = null;
        if (nodeDB.getCanToUp() && direction.equals(Direction.UP)) {
            nextNodeDB = nodeDBS.stream().filter(nodeDB1 -> nodeDB1.getGridx().equals(nodeDB.getGridx() - 1) && nodeDB1.getGridy().equals(nodeDB.getGridy()) && nodeDB1.getGridz().equals(nodeDB.getGridz())).findFirst();
        }
        if (nodeDB.getCanToDown() && direction.equals(Direction.DOWN)) {
            nextNodeDB = nodeDBS.stream().filter(nodeDB1 -> nodeDB1.getGridx().equals(nodeDB.getGridx() + 1) && nodeDB1.getGridy().equals(nodeDB.getGridy()) && nodeDB1.getGridz().equals(nodeDB.getGridz())).findFirst();
        }
        if (nodeDB.getCanToLeft() && direction.equals(Direction.LEFT)) {
            nextNodeDB = nodeDBS.stream().filter(nodeDB1 -> nodeDB1.getGridx().equals(nodeDB.getGridx()) && nodeDB1.getGridy().equals(nodeDB.getGridy() - 1) && nodeDB1.getGridz().equals(nodeDB.getGridz())).findFirst();
        }
        if (nodeDB.getCanToRight() && direction.equals(Direction.RIGHT)) {
            nextNodeDB = nodeDBS.stream().filter(nodeDB1 -> nodeDB1.getGridx().equals(nodeDB.getGridx()) && nodeDB1.getGridy().equals(nodeDB.getGridy() + 1) && nodeDB1.getGridz().equals(nodeDB.getGridz())).findFirst();
        }
        if (nextNodeDB != null && nextNodeDB.isPresent()) {
            if (nextNodeDB.get().getGridType().equals(NodeType.P)) {
                nextNodeDB.get().setSingleOrder(direction.getCode());
                nextNodeDB.get().setRoadNo(rodeNo);
                nodeDBList.add(nextNodeDB.get());
                nodeDBS.remove(nextNodeDB.get());
                mergeByDirection(rodeNo, nodeDBList, nodeDBS, nextNodeDB.get(), direction, singleDivide);
            } else if (nextNodeDB.get().getGridType().equals(NodeType.R)) {
                for (int i = 0; i < nodeDBList.size(); i++) {
                    if (singleDivide != null && singleDivide.size() > 0) {
                        if (nodeDBList.get(i).getCanToDown() || nodeDBList.get(i).getCanToUp()) {
                            ArrayList<Integer> gridx = new ArrayList<>();
                            nodeDBList.forEach(nodeDB1 -> gridx.add(nodeDB1.getGridx()));
                            for (int j : singleDivide) {
                                if (gridx.contains(j)) {
                                    if (nodeDBList.get(i).getGridx() <= j) {
                                        nodeDBList.get(i).setSingleOrder(Direction.DOWN.getCode());
                                        nodeDBList.get(i).setRoadNo(rodeNo + "-1");
                                    } else {
                                        nodeDBList.get(i).setSingleOrder(Direction.UP.getCode());
                                        nodeDBList.get(i).setRoadNo(rodeNo + "-2");
                                    }
                                }
                            }
                        } else if (nodeDBList.get(i).getCanToRight() || nodeDBList.get(i).getCanToLeft()) {
                            ArrayList<Integer> gridy = new ArrayList<>();
                            nodeDBList.forEach(nodeDB1 -> gridy.add(nodeDB1.getGridy()));
                            for (int j : singleDivide) {
                                if (gridy.contains(j)) {
                                    if (nodeDBList.get(i).getGridy() <= j) {
                                        nodeDBList.get(i).setSingleOrder(Direction.RIGHT.getCode());
                                        nodeDBList.get(i).setRoadNo(rodeNo + "-1");
                                    } else {
                                        nodeDBList.get(i).setSingleOrder(Direction.LEFT.getCode());
                                        nodeDBList.get(i).setRoadNo(rodeNo + "-2");
                                    }
                                }
                            }
                        }
                    }
                }
                nodeDBList.forEach(nodeDB1 -> nodeDB1.setRoadType("D"));
            } else {
                nextNodeDB.get().setRoadNo(rodeNo);
                nextNodeDB.get().setSingleOrder(direction.getCode());
                nodeDBList.add(nextNodeDB.get());
                nodeDBList.forEach(nodeDB1 -> nodeDB1.setRoadType("S"));
            }
        } else if (nextNodeDB == null) {
            nodeDBList.forEach(nodeDB1 -> nodeDB1.setRoadType("S"));
        }
    }

    /**
     * 检查单元格中的类型和方向
     *
     * @param x
     * @param y
     * @param z
     * @param cell
     * @param grid
     */
    private static void checkCell(int x, int y, int z, String cell, NodeDB grid) {
        grid.setCanToUp(false);
        grid.setCanToDown(false);
        grid.setCanToLeft(false);
        grid.setCanToRight(false);
        grid.setCanToStop(false);
        if (cell.contains("上")) {
            grid.setCanToUp(true);
        }
        if (cell.contains("下")) {
            grid.setCanToDown(true);
        }
        if (cell.contains("左")) {
            grid.setCanToLeft(true);
        }
        if (cell.contains("右")) {
            grid.setCanToRight(true);
        }
        //读取单元格位移量
        int index1 = cell.indexOf("{");
        int index2 = cell.indexOf("}");
        if (index1 != -1 && index2 != -1) {
            String offsetXY = cell.substring(index1 + 1, index2);
            String[] offsets = offsetXY.split(",");
            if (offsets.length == 2) {
                if (!offsets[0].trim().equals("")) {
                    int offsetX = Integer.parseInt(offsets[0].trim());
                    grid.setOffsetx(offsetX);
                }
                if (!offsets[1].trim().equals("")) {
                    int offsetY = Integer.parseInt(offsets[1].trim());
                    grid.setOffsety(offsetY);
                }
            } else {
                throw new IllegalCellException(new Node(x, y - 2, z), "单元格位移量数据异常！" + cell);
            }
        }
        // 读取单元格编号
        int index3 = cell.indexOf("<");
        int index4 = cell.indexOf(">");
        if (index3 != -1 && index4 != -1) {
            String gridId = cell.substring(index3 + 1, index4);
            grid.setGridId(gridId);
        }
        if ("B".equals(cell)) {
            grid.setGridType(NodeType.B);
        } else if (cell.contains("货")) {
            grid.setGridType(NodeType.P);
            grid.setCanToStop(true);
        } else if (cell.contains("提")) {
            grid.setGridType(NodeType.L);
            grid.setCanToStop(true);
        } else if (cell.contains("道")) {
            grid.setGridType(NodeType.R);
            grid.setCanToStop(true);
        } else if (cell.contains("充")) {
            grid.setGridType(NodeType.C);
            grid.setCanToStop(true);
        } else if (cell.contains("入")) {
            grid.setGridType(NodeType.CONNECTION);
            grid.setCanToStop(true);
        } else if (cell.contains("出")) {
            grid.setGridType(NodeType.CONNECTION);
            grid.setCanToStop(true);
        } else if (cell.contains("输")) {
            grid.setGridType(NodeType.ST);
        } else if (cell.contains("穿")) {
            grid.setGridType(NodeType.TL);
            grid.setCanToStop(true);
        } else if (cell.contains("门")) {
            grid.setGridType(NodeType.AD);
        } else if (cell.contains("工")) {
            grid.setGridType(NodeType.ST);
        } else if (cell.contains("接")) {
            grid.setGridType(NodeType.CONNECTION);
            grid.setCanToStop(true);
        } else {
            throw new IllegalCellException(new Node(x, y, z), "不合法:" + cell);
        }
        if (cell.contains("门")) {
            grid.setIsDoor(true);
            grid.setCanToStop(false);
        }

        analyzeRgv60Point(grid, cell);
        analyzeRgvRollerDirection(grid, cell);
        analyzeRgv60Tag(grid, cell);
        analyzeRgvGroundCode(grid, cell);
        analyzeSonRgvXMovement(grid, cell);
        analyzeSonRgvYMovement(grid, cell);

    }

    /**
     * 导入地码
     * @param grid
     * @param cell
     */
    private static void analyzeRgvGroundCode(NodeDB grid, String cell) {
        int indexStart = cell.indexOf("@.");
        int indexEnd = cell.indexOf(".@");

        if (indexStart == -1 || indexEnd == -1) {
            return ;
        }

        String groundCode = cell.substring(indexStart + 2, indexEnd);

        grid.setGroundCode(groundCode);
    }
    /**
     * 导入子车位移量
     * @param grid
     * @param cell
     */
    private static void analyzeSonRgvXMovement(NodeDB grid, String cell) {
        int indexStart = cell.indexOf("X$");
        int indexEnd = cell.indexOf("$X");

        if (indexStart == -1 || indexEnd == -1) {
            return ;
        }

        Integer movement = Integer.parseInt(cell.substring(indexStart + 2, indexEnd));

        grid.setOffsetx(movement);
    }
    /**
     * 导入子车位移量
     * @param grid
     * @param cell
     */
    private static void analyzeSonRgvYMovement(NodeDB grid, String cell) {
        int indexStart = cell.indexOf("Y$");
        int indexEnd = cell.indexOf("$Y");

        if (indexStart == -1 || indexEnd == -1) {
            return ;
        }

        Integer movement = Integer.parseInt(cell.substring(indexStart + 2, indexEnd));

        grid.setOffsety(movement);
    }
    /**
     * 解析6代车点位
     * @param grid
     * @param cell
     */
    private static void analyzeRgv60Point(NodeDB grid, String cell) {
        int indexStart = cell.indexOf("*.");
        int indexEnd = cell.indexOf(".*");

        if (indexStart == -1 || indexEnd == -1) {
            return ;
        }

        String point = cell.substring(indexStart + 2, indexEnd);

        grid.setRgvPoint(point);
    }
    /**
     * 解析6代车tag点位
     * @param grid
     * @param cell
     */
    private static void analyzeRgv60Tag(NodeDB grid, String cell) {
        int indexStart = cell.indexOf("#.");
        int indexEnd = cell.indexOf(".#");

        if (indexStart == -1 || indexEnd == -1) {
            return ;
        }

        Integer tag = Integer.parseInt(cell.substring(indexStart + 2, indexEnd));

        grid.setTag(tag);
    }
    /**
     * 解析滚筒车方向
     * @param grid
     * @param cell
     */
    private static void analyzeRgvRollerDirection(NodeDB grid, String cell) {
        int indexStart = cell.indexOf("[");
        int indexEnd = cell.indexOf("]");

        if (indexStart == -1 || indexEnd == -1) {
            return ;
        }

        Integer direction=0;
        if(cell.substring(indexStart + 1, indexEnd).equals("left")){
            direction=1;
        }else if(cell.substring(indexStart + 1, indexEnd).equals("right")){
            direction=2;
        }
        grid.setDirection(direction);
    }

    /**
     * 返回异常原因
     *
     * @param sheetNo
     * @param rowNo
     * @param cellNo
     * @param message
     * @return
     */
    private String excelExceptionMessage(Integer sheetNo, Integer rowNo, Integer cellNo, String message) {
        throw new RuntimeException("第" + (sheetNo + 1) + "个sheet中，第" + rowNo + "行第" + cellNo + "列存在异常:" + message);
    }
}
